﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;
using MS.Utility.Config;
using MS.Utility.Files;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using OfficeOpenXml;

namespace MS.Utility.Files
{
    public class ExcelHelper
    {
        #region 导出Excel
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="lists">需要导出的洗泛型数据</param>
        /// <param name="head">中文列名对照</param>
        /// <param name="fileName">保存路径</param>
        public static void Export<T>(List<T> lists, Dictionary<string, string> head, string fileName)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                MemoryStream ms = new MemoryStream();
                HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
                HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
                int j = 1;
                bool firstRow = false;
                Type type = typeof(T);
                PropertyInfo[] properties = type.GetProperties();

                foreach (T item in lists)
                {
                    HSSFRow dataRow = sheet.CreateRow(j) as HSSFRow;
                    int i = 0;
                    foreach (var column in head)
                    {
                        if (i >= head.Count) break;
                        var propertie = properties.Where(o => o.Name == column.Key.ToString()).FirstOrDefault();
                        if (propertie != null)
                        {
                            if (!firstRow)
                            {
                                headerRow.CreateCell(i).SetCellValue(column.Value.ToString());
                            }
                            DataAdd(dataRow, propertie.PropertyType, propertie.GetValue(item, null), i);
                            i++;
                        }
                    }
                    firstRow = true;
                    j++;
                }
                workbook.Write(ms);
                HttpContext.Current.Response.ContentType = "application/octet-stream";
                HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", string.IsNullOrEmpty(fileName) ? DateTime.Now.ToString("yyyyMMddHHmmssfff") : fileName));
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                workbook = null;
                ms.Close();
                ms.Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 导出Excel到指定路径
        /// </summary>
        /// <param name="lists">需要导出的洗泛型数据</param>
        /// <param name="head">中文列名对照</param>
        /// <param name="fileName">保存路径</param>
        public static void ExportToPath<T>(List<T> lists, Dictionary<string, string> head,string filePath, string fileName)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                MemoryStream ms = new MemoryStream();
                HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
                HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
                int j = 1;
                bool firstRow = false;
                Type type = typeof(T);
                PropertyInfo[] properties = type.GetProperties();

                foreach (T item in lists)
                {
                    HSSFRow dataRow = sheet.CreateRow(j) as HSSFRow;
                    int i = 0;
                    foreach (var column in head)
                    {
                        if (i >= head.Count) break;
                        var propertie = properties.Where(o => o.Name == column.Key.ToString()).FirstOrDefault();
                        if (propertie != null)
                        {
                            if (!firstRow)
                            {
                                headerRow.CreateCell(i).SetCellValue(column.Value.ToString());
                            }
                            DataAdd(dataRow, propertie.PropertyType, propertie.GetValue(item, null), i);
                            i++;
                        }
                    }
                    firstRow = true;
                    j++;
                }

                workbook.Write(ms);
                FileUtility.CreateFileDirectory(filePath, ConfigSetting.FileAccessType);
                FileStream fs = new FileStream(string.Format("{0}{1}.xls", filePath, string.IsNullOrEmpty(fileName) ? DateTime.Now.ToString("yyyyMMddHHmmssfff") : fileName), FileMode.CreateNew, FileAccess.Write);
                fs.Write(ms.ToArray(), 0, ms.ToArray().Length);
                fs.Close();
                fs.Dispose();
                ms.Close();
                ms.Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        private static void DataAdd(HSSFRow dataRow, Type type, object value, int i)
        {
            if (value == null)
            {
                dataRow.CreateCell(i).SetCellValue(string.Empty);
                return;
            }
            if (string.IsNullOrEmpty(value.ToString()))
            {
                dataRow.CreateCell(i).SetCellValue(string.Empty);
                return;
            }
            var typeName = type.Name;
            switch (typeName)
            {
                case "Int16":
                case "Int32":
                case "Int64":
                    dataRow.CreateCell(i).SetCellValue(Convert.ToInt32(value));
                    break;
                case "Float":
                case "Double":
                case "Decimal":
                    dataRow.CreateCell(i).SetCellValue(Convert.ToDouble(value));
                    break;
                case "DateTime":
                    dataRow.CreateCell(i).SetCellValue(value.ToString());
                    break;
                default:
                    dataRow.CreateCell(i).SetCellValue(value.ToString());
                    break;
            }
        }

        /// <summary>
        /// 导出EXCEL
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fileName"></param>
        public static void Export(DataTable dataTable, string fileName)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                MemoryStream ms = new MemoryStream();
                HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
                HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
                int i = 0;

                foreach (DataColumn dc in dataTable.Columns)
                {
                    headerRow.CreateCell(i).SetCellValue(dc.ColumnName);
                    i++;
                }

                int j = 0;
                i = 1;
                foreach (DataRow dr in dataTable.Rows)
                {
                    HSSFRow dataRow = sheet.CreateRow(i) as HSSFRow;
                    j = 0;
                    foreach (DataColumn dc in dataTable.Columns)
                    {
                        DataAdd(dataRow, dc.DataType, dr[dc], j);
                        j++;
                    }
                    i++;
                }

                workbook.Write(ms);
                HttpContext.Current.Response.ContentType = "application/octet-stream";
                HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", string.IsNullOrEmpty(fileName) ? DateTime.Now.ToString("yyyyMMddHHmmssfff") : fileName));
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                workbook = null;
                ms.Close();
                ms.Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 导出EXCEL
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fileName"></param>
        public static void Export(HSSFWorkbook workbook, string fileName)
        {
            try
            {
                MemoryStream ms = new MemoryStream();
                workbook.Write(ms);
                HttpContext.Current.Response.ContentType = "application/octet-stream";
                HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", string.IsNullOrEmpty(fileName) ? DateTime.Now.ToString("yyyyMMddHHmmssfff") : fileName));
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                workbook = null;
                ms.Close();
                ms.Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region 导入Excel
        /// <summary>
        /// 导入Excel-支持03-07
        /// </summary>
        /// <param name="head">中文列名对照</param>
        /// <param name="stream">导入流</param>
        /// <param name="sheetNo">Sheet页码（页码从0开始）</param>
        /// <returns></returns>
        public static List<T> ImportXls<T>(Hashtable head, Stream stream, int sheetNo)
        {
            try
            {
                HSSFWorkbook hssfworkbook;
                List<T> lists = new List<T>();
                hssfworkbook = new HSSFWorkbook(stream);
                NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(sheetNo);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                HSSFRow headerRow = sheet.GetRow(0) as HSSFRow;

                PropertyInfo[] properties;
                T t = default(T);
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    HSSFRow row = sheet.GetRow(i) as HSSFRow;
                    if (row == null)
                    {
                        continue;
                    }
                    t = Activator.CreateInstance<T>();
                    properties = t.GetType().GetProperties();
                    foreach (PropertyInfo column in properties)
                    {
                        int j = headerRow.Cells.FindIndex(delegate(ICell c)
                        {
                            return c.StringCellValue == (head[column.Name] == null ? column.Name : head[column.Name].ToString());
                        });
                        if (j >= 0 && row.GetCell(j) != null)
                        {
                            object value = ValueType(column.PropertyType, row.GetCell(j).ToString());
                            column.SetValue(t, value, null);
                        }
                    }
                    lists.Add(t);
                }

                return lists;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 导入Excel-支持10-13
        /// </summary>
        /// <param name="head">中文列名对照</param>
        /// <param name="stream">导入流</param>
        /// <param name="sheetNo">Sheet页码(页码从1开始)</param>
        /// <returns></returns>
        public static List<T> ImportXlsx<T>(Hashtable head, Stream stream, int sheetNo)
        {
            List<T> lists = new List<T>();
            using (ExcelPackage package = new ExcelPackage(stream))
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets[sheetNo];
                var headerRow = sheet.Cells.Take(sheet.Dimension.End.Column).ToList();
                PropertyInfo[] properties;
                T t = default(T);
                for (int i = sheet.Dimension.Start.Row + 1; i <= sheet.Dimension.End.Row; i++)
                {
                    ExcelRow excelRow = sheet.Row(i);
                    t = Activator.CreateInstance<T>();
                    properties = t.GetType().GetProperties();
                    foreach (PropertyInfo column in properties)
                    {
                        int j = headerRow.FindIndex(delegate(ExcelRangeBase c)
                        {
                            return c.Value.ToString() == (head[column.Name] == null ? column.Name : head[column.Name].ToString());
                        }) + 1;
                        if (j >= 1 && sheet.Cells[i, j].Value != null)
                        {
                            object value = ValueType(column.PropertyType, sheet.Cells[i, j].Value.ToString());
                            column.SetValue(t, value, null);
                        }
                    }
                    lists.Add(t);
                }
            }
            return lists;
        }

        private static object ValueType(Type type, string value)
        {
            object obj = null;
            if (value == null) return value;
            var typeName = type.Name;

            switch (typeName)
            {
                case "Int16":
                case "Int32":
                case "Int64":
                    obj = int.Parse(value);
                    break;
                case "Float":
                case "Double":
                case "Decimal":
                    obj = double.Parse(value);
                    break;
                case "Single":
                    obj = Single.Parse(value);
                    break;
                case "DateTime":
                    obj = DateTime.Parse(value);
                    break;
                default:
                    obj = value.ToString();
                    break;
            }
            return obj;

        }
        #endregion
    }
}
